Imports Microsoft.VisualBasic
Imports Oracle.DataAccess.Client
Imports Oracle.DataAccess.Types
Imports System.Data
Imports DataAccesGenerales
Imports DataAcces

Public Class DataAccesPesajeAduana
	Private Const consTimeOut As Integer = 0 

    ''' <summary>
    ''' Funcion para Insertar un regristro en la tabla PesajeAduana
    ''' </summary>
	Public Function PesajeAduana_InsertarModificar(pfolio_autoriza As String, pnum_oficio As String, ppatente As String, ppedimento As String, pclave_recinto As String, ptipo_mercancia As String, pumt As Integer, pcs_autorizadas As Integer, pvid As Long, ppeso_pedimento As Double, pcs_termino As Double, peta As String, pfecha_registro As String, pfecha_registro_autoriza As String, pfecha_registro_cierre As String, pfecha_cierre As String, pfecha_pago As String, pfecha_autorizacion As String, pfecha_termino As String, pstatus As String, pexcedente As String, penvio As String, pfraccion As String, prfc_impo_expo As String, pbl As String, pusuario_autorizo As String, pusuario_cierre As String, pnumero_buzon_cierre As String, pbuque As String, ppuerto_origen_destino As String, pnombre_impo_expo As String, pdesc_mercancia As String, pobservaciones As String) As Byte
        Dim pTransaccion As Byte = 0
        Dim mensaje As String = String.Empty
        Try
            Using oraConnection As New Oracle.DataAccess.Client.OracleConnection(ConfigurationManager.ConnectionStrings("EDIV2DESOraClient").ConnectionString)
                Using oraCommandDocumentos As New Oracle.DataAccess.Client.OracleCommand()
                    oraCommandDocumentos.Connection = oraConnection
                    oraCommandDocumentos.CommandTimeout = consTimeOut
                    oraCommandDocumentos.CommandType = CommandType.StoredProcedure
                    oraCommandDocumentos.CommandText = "EDIV2.?NomPaquete.PesajeAduanaInsertarModificar"
          oraCommandDocumentos.Parameters.Add("pfolio_autoriza", pfolio_autoriza)
          oraCommandDocumentos.Parameters.Add("pnum_oficio", pnum_oficio)
          oraCommandDocumentos.Parameters.Add("ppatente", ppatente)
          oraCommandDocumentos.Parameters.Add("ppedimento", ppedimento)
          oraCommandDocumentos.Parameters.Add("pclave_recinto", pclave_recinto)
          oraCommandDocumentos.Parameters.Add("ptipo_mercancia", ptipo_mercancia)
          oraCommandDocumentos.Parameters.Add("pumt", pumt)
          oraCommandDocumentos.Parameters.Add("pcs_autorizadas", pcs_autorizadas)
          oraCommandDocumentos.Parameters.Add("pvid", pvid)
          oraCommandDocumentos.Parameters.Add("ppeso_pedimento", ppeso_pedimento)
          oraCommandDocumentos.Parameters.Add("pcs_termino", pcs_termino)
          oraCommandDocumentos.Parameters.Add("peta", peta)
          oraCommandDocumentos.Parameters.Add("pfecha_registro", pfecha_registro)
          oraCommandDocumentos.Parameters.Add("pfecha_registro_autoriza", pfecha_registro_autoriza)
          oraCommandDocumentos.Parameters.Add("pfecha_registro_cierre", pfecha_registro_cierre)
          oraCommandDocumentos.Parameters.Add("pfecha_cierre", pfecha_cierre)
          oraCommandDocumentos.Parameters.Add("pfecha_pago", pfecha_pago)
          oraCommandDocumentos.Parameters.Add("pfecha_autorizacion", pfecha_autorizacion)
          oraCommandDocumentos.Parameters.Add("pfecha_termino", pfecha_termino)
          oraCommandDocumentos.Parameters.Add("pstatus", pstatus)
          oraCommandDocumentos.Parameters.Add("pexcedente", pexcedente)
          oraCommandDocumentos.Parameters.Add("penvio", penvio)
          oraCommandDocumentos.Parameters.Add("pfraccion", pfraccion)
          oraCommandDocumentos.Parameters.Add("prfc_impo_expo", prfc_impo_expo)
          oraCommandDocumentos.Parameters.Add("pbl", pbl)
          oraCommandDocumentos.Parameters.Add("pusuario_autorizo", pusuario_autorizo)
          oraCommandDocumentos.Parameters.Add("pusuario_cierre", pusuario_cierre)
          oraCommandDocumentos.Parameters.Add("pnumero_buzon_cierre", pnumero_buzon_cierre)
          oraCommandDocumentos.Parameters.Add("pbuque", pbuque)
          oraCommandDocumentos.Parameters.Add("ppuerto_origen_destino", ppuerto_origen_destino)
          oraCommandDocumentos.Parameters.Add("pnombre_impo_expo", pnombre_impo_expo)
          oraCommandDocumentos.Parameters.Add("pdesc_mercancia", pdesc_mercancia)
          oraCommandDocumentos.Parameters.Add("pobservaciones", pobservaciones)

                    oraCommandDocumentos.Parameters.Add("pTransaccion", Oracle.DataAccess.Client.OracleDbType.Byte).Direction = ParameterDirection.Output
                    oraCommandDocumentos.Parameters.Add("pMensaje", Oracle.DataAccess.Client.OracleDbType.Varchar2, 200).Direction = ParameterDirection.Output

                    oraConnection.Open()
                    oraCommandDocumentos.ExecuteNonQuery()
                    pTransaccion = CByte(oraCommandDocumentos.Parameters("pTransaccion").Value.ToString())
                    mensaje = oraCommandDocumentos.Parameters("pMensaje").Value.ToString()
                    oraConnection.Close()
                End Using
            End Using
        Catch ex As Exception
            Throw ex
        End Try
        Return pTransaccion
    End Function
  
    ''' <summary>
    ''' Funcion para consultar un registro de la tabla PesajeAduana
    ''' </summary>
    ''' <param name="pNidDocumento">numero de documento</param>
    ''' <returns>DataTable</returns>
    ''' <remarks></remarks>
   Public Function PesajeAduana_Consultar(pNidDocumento As Long) As DataTable
        Dim dtResultado As New DataTable("PesajeAduanaConsulta")
        Try
            Using oraConnection As New Oracle.DataAccess.Client.OracleConnection(ConfigurationManager.ConnectionStrings("EDIV2DESOraClient").ConnectionString)
                Using oraCommandDocumentos As New Oracle.DataAccess.Client.OracleCommand()
                    oraCommandDocumentos.Connection = oraConnection
                    oraCommandDocumentos.CommandTimeout = consTimeOut
                    oraCommandDocumentos.CommandType = CommandType.StoredProcedure
                    oraCommandDocumentos.CommandText = "EDIV2.?NomPaquete.PesajeAduanaConsultar"
                    oraCommandDocumentos.Parameters.Add("pNidDocumento", pNidDocumento)
                    oraCommandDocumentos.Parameters.Add("pTransaccion", Oracle.DataAccess.Client.OracleDbType.Byte).Direction = ParameterDirection.Output
                    oraCommandDocumentos.Parameters.Add(New Oracle.DataAccess.Client.OracleParameter("pCursor", OracleDbType.RefCursor)).Direction = ParameterDirection.Output

                    Dim oraDataAdapter As New OracleDataAdapter(oraCommandDocumentos)
                    oraDataAdapter.Fill(dtResultado)
                    oraConnection.Close()
                End Using
            End Using
        Catch ex As Exception
            Throw ex
        End Try
        Return dtResultado
    End Function
  
    ''' <summary>
    ''' Funcion para generar la cadena XML Chilkat del documento de la tabla PesajeAduana
    ''' </summary>
    ''' <param name="nidDocumento"> numero del documento</param>
    ''' <returns>Chilkat.Xml</returns>
    ''' <remarks></remarks>
   Public Function PesajeAduana_CadenaOriginalXMLChilkat(nidDocumento As Long) As Chilkat.Xml
        Dim detalleDataSet As New DataSet("DatosPesajeAduana")
        Try
      detalleDataSet = PesajeAduana_DetalleXML(nidDocumento)
            'En esta parte van las tablas obtenidas en la consulta anterior
            'separadas dependiendo del reporte a crear
            detalleDataSet.Tables(0).TableName = "Informacion del Tramite"
            detalleDataSet.Tables(1).TableName = "Datos PesajeAduana"
            detalleDataSet.Tables(2).TableName = "Archivos Anexos"
            detalleDataSet.Tables(3).TableName = "Incidencias"

            Dim xml As New Chilkat.Xml
            xml.LoadXml(detalleDataSet.GetXml())
            Return xml 'detalleDataSet.GetXml()
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    ''' <summary>
    ''' Funcion para consultar el detalle del documento de la tabla PesajeAduana
    ''' </summary>
    ''' <param name="pNidDocumento">numero del documento</param>
    ''' <returns>DataSet</returns>
    ''' <remarks></remarks>
	Public Function PesajeAduana_DetalleXML(pNidDocumento As Long) As DataSet
        Dim dsDetalle As New DataSet("PesajeAduanaDetalle")
        Dim pTransaccion As Byte = 0
        Try
            Using oraConnection As New Oracle.DataAccess.Client.OracleConnection(ConfigurationManager.ConnectionStrings("EDIV2DESOraClient").ConnectionString)
                Using oraCommandDocumentos As New Oracle.DataAccess.Client.OracleCommand()
                    oraCommandDocumentos.Connection = oraConnection
                    oraCommandDocumentos.CommandTimeout = consTimeOut
                    oraCommandDocumentos.CommandType = CommandType.StoredProcedure
                    oraCommandDocumentos.CommandText = "EDIV2.?NomPaquete.PesajeAduanaDetalleXML"
                    oraCommandDocumentos.Parameters.Add("pNidDocumento", pNidDocumento)
                    oraCommandDocumentos.Parameters.Add("pTransaccion", Oracle.DataAccess.Client.OracleDbType.Byte).Direction = ParameterDirection.Output
                    oraCommandDocumentos.Parameters.Add(New Oracle.DataAccess.Client.OracleParameter("pEncabezado", OracleDbType.RefCursor)).Direction = ParameterDirection.Output
                    oraCommandDocumentos.Parameters.Add(New Oracle.DataAccess.Client.OracleParameter("pDatos", OracleDbType.RefCursor)).Direction = ParameterDirection.Output
                    oraCommandDocumentos.Parameters.Add(New Oracle.DataAccess.Client.OracleParameter("pAnexos", OracleDbType.RefCursor)).Direction = ParameterDirection.Output
                    oraCommandDocumentos.Parameters.Add(New Oracle.DataAccess.Client.OracleParameter("pIncidencias", OracleDbType.RefCursor)).Direction = ParameterDirection.Output

                    Dim oraDataAdapter As New OracleDataAdapter(oraCommandDocumentos)
                    'pTransaccion = CByte(oraCommandDocumentos.Parameters("pTransaccion").Value.ToString())
                    oraDataAdapter.Fill(dsDetalle)
                End Using
            End Using
        Catch ex As Exception
            Throw ex
        End Try
        Return dsDetalle
    End Function
  
	Public Function PesajeAduana_DatosBase() As DataSet
        Dim dtResultado As New DataSet("PesajeAduana_DatosBase")
        Try
            Using oraConnection As New Oracle.DataAccess.Client.OracleConnection(ConfigurationManager.ConnectionStrings("EDIV2DESOraClient").ConnectionString)
                Using oraCommandDocumentos As New Oracle.DataAccess.Client.OracleCommand()
                    oraCommandDocumentos.Connection = oraConnection
                    oraCommandDocumentos.CommandTimeout = consTimeOut
                    oraCommandDocumentos.CommandType = CommandType.StoredProcedure
                    oraCommandDocumentos.CommandText = "EDIV2.?NomPaquete.PesajeAduanaDatosBase"
                    oraCommandDocumentos.Parameters.Add(New Oracle.DataAccess.Client.OracleParameter("pCursor", OracleDbType.RefCursor)).Direction = ParameterDirection.Output

                    Dim oraDataAdapter As New OracleDataAdapter(oraCommandDocumentos)
                    oraDataAdapter.Fill(dtResultado)
                    oraConnection.Close()
                End Using
            End Using
        Catch ex As Exception
            Throw ex
        End Try
        Return dtResultado
    End Function

    ''' <summary>
    ''' Funcion para generar un combo de la tabla PesajeAduana
    ''' </summary>
    ''' <param name="pNombre">nombre a buscar en la tabla</param>
    ''' <returns>DataTable</returns>
    ''' <remarks>si el nombre es "" retornara todos los registros</remarks>
	Public Function PesajeAduana_Combo(pNombre As String) As DataTable
        Dim dtResultado As New DataTable("PesajeAduanaCombo")
        Try
            Using oraConnection As New Oracle.DataAccess.Client.OracleConnection(ConfigurationManager.ConnectionStrings("EDIV2DESOraClient").ConnectionString)
                Using oraCommandDocumentos As New Oracle.DataAccess.Client.OracleCommand()
                    oraCommandDocumentos.Connection = oraConnection
                    oraCommandDocumentos.CommandTimeout = consTimeOut
                    oraCommandDocumentos.CommandType = CommandType.StoredProcedure
                    oraCommandDocumentos.CommandText = "EDIV2.?NomPaquete.PesajeAduanaCombo"
                    oraCommandDocumentos.Parameters.Add("pNombre", pNombre)
                    oraCommandDocumentos.Parameters.Add("pTransaccion", Oracle.DataAccess.Client.OracleDbType.Byte).Direction = ParameterDirection.Output
                    oraCommandDocumentos.Parameters.Add(New Oracle.DataAccess.Client.OracleParameter("pCursor", OracleDbType.RefCursor)).Direction = ParameterDirection.Output

                    Dim oraDataAdapter As New OracleDataAdapter(oraCommandDocumentos)
                    oraDataAdapter.Fill(dtResultado)
                    oraConnection.Close()
                End Using
            End Using
        Catch ex As Exception
            Throw ex
        End Try
        Return dtResultado
    End Function

End Class
